package br.com.localfloripa.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

import javax.inject.Inject;
import javax.inject.Named;
import javax.persistence.Query;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import br.com.localfloripa.dto.AuditoriaDto;
import br.com.localfloripa.entities.Auditoria;
import br.com.localfloripa.entities.TipoAuditoria;
import br.com.localfloripa.entities.Usuario;

@Named("auditoriaDao")
public class AuditoriaDao extends DaoManager<Auditoria> {
	
	private static final Logger log = LoggerFactory.getLogger(AuditoriaDao.class);
	private ResourceBundle bundle = ResourceBundle.getBundle("messages.MessageBundle");
	
	@Inject
	@Named("representanteDao")
	private RepresentanteDao representanteDao;
	
	public Auditoria findById(Long idAuditoria) throws Exception {
		return em.find(Auditoria.class, idAuditoria);
	}
	
	public List<AuditoriaDto> listAuditoriaPaginadaBySearch(String searchField,int pagina, int max) throws Exception {
		
		Query query = em.createNativeQuery("SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.cliente_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".servico s ON s.id = a.servico_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".ramo r ON r.id = a.ramo_id " +
				"WHERE UPPER(pf.nome) LIKE ? OR UPPER(s.nome) LIKE ? OR UPPER(r.nome) LIKE ? OR UPPER(pj.nome) LIKE ? " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome");
		query.setParameter(1, TipoAuditoria.S.name());
		query.setParameter(2, TipoAuditoria.F.name());
		query.setParameter(3, TipoAuditoria.T.name());
		query.setParameter(4, "%"+searchField.toUpperCase()+"%");
		query.setParameter(5, "%"+searchField.toUpperCase()+"%");
		query.setParameter(6, "%"+searchField.toUpperCase()+"%");
		query.setParameter(7, "%"+searchField.toUpperCase()+"%");	
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		List<AuditoriaDto> auditorias = new ArrayList<AuditoriaDto>();
		List<Object> objectList = query.getResultList();		
		for (Object object : objectList) {
			Object[] arrayObject = (Object[]) object;
			
			AuditoriaDto auditoria = new AuditoriaDto();			
			auditoria.setClienteId(((BigInteger) arrayObject[0]).longValue());
			auditoria.setClienteNome((String) arrayObject[1]);
			auditoria.setAnuncioId(((BigInteger) arrayObject[2]).longValue());
			auditoria.setAnuncioNome((String) arrayObject[3]);
			auditoria.setCliquesSite(((BigInteger) arrayObject[4]).longValue());
			auditoria.setCliquesAlbum(((BigInteger) arrayObject[5]).longValue());
			auditoria.setCliquesTelefone(((BigInteger) arrayObject[6]).longValue());
			
			auditorias.add(auditoria);
		}		
		return auditorias;	
		
		
	}
	
	public Long countListAuditoriaBySearch(String searchField) {
		Query query = em.createNativeQuery("SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.cliente_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".servico s ON s.id = a.servico_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".ramo r ON r.id = a.ramo_id " +
				"WHERE UPPER(pf.nome) LIKE ? OR UPPER(s.nome) LIKE ? OR UPPER(r.nome) LIKE ? OR UPPER(pj.nome) LIKE ? " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome");
		query.setParameter(1, TipoAuditoria.S.name());
		query.setParameter(2, TipoAuditoria.F.name());
		query.setParameter(3, TipoAuditoria.T.name());
		query.setParameter(4, "%"+searchField.toUpperCase()+"%");
		query.setParameter(5, "%"+searchField.toUpperCase()+"%");
		query.setParameter(6, "%"+searchField.toUpperCase()+"%");	
		query.setParameter(7, "%"+searchField.toUpperCase()+"%");
		
		return new Long(query.getResultList().size());
	}
	
	public List<AuditoriaDto> listAuditoriaPaginada(int pagina, int max) throws Exception {
		
		Query query = em.createNativeQuery("SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.cliente_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome");
		query.setParameter(1, TipoAuditoria.S.name());
		query.setParameter(2, TipoAuditoria.F.name());
		query.setParameter(3, TipoAuditoria.T.name());
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		List<AuditoriaDto> auditorias = new ArrayList<AuditoriaDto>();
		List<Object> objectList = query.getResultList();		
		for (Object object : objectList) {
			Object[] arrayObject = (Object[]) object;
			
			AuditoriaDto auditoria = new AuditoriaDto();		
			auditoria.setClienteId(((BigInteger) arrayObject[0]).longValue());
			auditoria.setClienteNome((String) arrayObject[1]);
			auditoria.setAnuncioId(((BigInteger) arrayObject[2]).longValue());
			auditoria.setAnuncioNome((String) arrayObject[3]);
			auditoria.setCliquesSite(((BigInteger) arrayObject[4]).longValue());
			auditoria.setCliquesAlbum(((BigInteger) arrayObject[5]).longValue());
			auditoria.setCliquesTelefone(((BigInteger) arrayObject[6]).longValue());
			
			auditorias.add(auditoria);
		}		
		return auditorias;	
	}
	
	public Long countListAuditoria() {
		
		Query query = em.createNativeQuery("SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.cliente_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome");
		query.setParameter(1, TipoAuditoria.S.name());
		query.setParameter(2, TipoAuditoria.F.name());
		query.setParameter(3, TipoAuditoria.T.name());
		
		return new Long(query.getResultList().size());
	}
	
	
	public List<AuditoriaDto> listAuditoriaByLoggedUserPaginada(int pagina, int max) throws Exception {
		
		String sql = null;
		Long id = null;
		if (representanteDao.findById(Usuario.getLoggedUser().getCliente().getId()) != null) { //corretor
			sql = "SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
					"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
					"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
					"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
					"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
					"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
					"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
					"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
					"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.representante_id " +
					"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
					"JOIN "+this.bundle.getString("jdbc.schema")+".usuario u ON u.cliente_id = c.id AND c.id = ? " +
					"GROUP BY c.id,pj.nome,a.id,pf.nome " +
					"ORDER BY pj.nome";
			id = Usuario.getLoggedUser().getCliente().getId();
		} else {
			sql = "SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.cliente_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".usuario u ON u.cliente_id = c.id AND u.id = ? " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome";
			id = Usuario.getLoggedUser().getId();
		}
		
		Query query = em.createNativeQuery(sql);
		query.setParameter(1, TipoAuditoria.S.name());
		query.setParameter(2, TipoAuditoria.F.name());
		query.setParameter(3, TipoAuditoria.T.name());
		query.setParameter(4, id);
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		List<AuditoriaDto> auditorias = new ArrayList<AuditoriaDto>();
		List<Object> objectList = query.getResultList();		
		for (Object object : objectList) {
			Object[] arrayObject = (Object[]) object;
			
			AuditoriaDto auditoria = new AuditoriaDto();	
			auditoria.setClienteId(((BigInteger) arrayObject[0]).longValue());
			auditoria.setClienteNome((String) arrayObject[1]);
			auditoria.setAnuncioId(((BigInteger) arrayObject[2]).longValue());
			auditoria.setAnuncioNome((String) arrayObject[3]);
			auditoria.setCliquesSite(((BigInteger) arrayObject[4]).longValue());
			auditoria.setCliquesAlbum(((BigInteger) arrayObject[5]).longValue());
			auditoria.setCliquesTelefone(((BigInteger) arrayObject[6]).longValue());
			
			auditorias.add(auditoria);
		}		
		return auditorias;	
	}
	
	public Long countListAuditoriaByLoggedUser() throws Exception {
		
		String sql = null;
		Long id = null;
		if (representanteDao.findById(Usuario.getLoggedUser().getCliente().getId()) != null) { // corretor
			sql = "SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.representante_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".usuario u ON u.cliente_id = c.id AND c.id = ? " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome";
			id = Usuario.getLoggedUser().getCliente().getId();
		} else {
			sql = "SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.cliente_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".usuario u ON u.cliente_id = c.id AND u.id = ? " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome";
			id = Usuario.getLoggedUser().getId();
		}
		
		Query query = em.createNativeQuery(sql);
		query.setParameter(1, TipoAuditoria.S.name());
		query.setParameter(2, TipoAuditoria.F.name());
		query.setParameter(3, TipoAuditoria.T.name());
		query.setParameter(4, id);
		
		return new Long(query.getResultList().size());
	}
	
	public List<AuditoriaDto> listAuditoriaPaginadaByLoggedUserSearch(String searchField,int pagina, int max) throws Exception {
		
		String sql = null;
		Long id = null;
		if (representanteDao.findById(Usuario.getLoggedUser().getCliente().getId()) != null) { // corretor
			sql = "SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.representante_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".servico s ON s.id = a.servico_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".ramo r ON r.id = a.ramo_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".usuario u ON u.cliente_id = c.id AND c.id = ? " +
				"WHERE UPPER(pf.nome) LIKE ? OR UPPER(s.nome) LIKE ? OR UPPER(r.nome) LIKE ? OR UPPER(pj.nome) LIKE ? " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome";
			id = Usuario.getLoggedUser().getCliente().getId();
		} else {
			sql = "SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.cliente_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".servico s ON s.id = a.servico_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".ramo r ON r.id = a.ramo_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".usuario u ON u.cliente_id = c.id AND u.id = ? " +
				"WHERE UPPER(pf.nome) LIKE ? OR UPPER(s.nome) LIKE ? OR UPPER(r.nome) LIKE ? OR UPPER(pj.nome) LIKE ? " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome";
			id = Usuario.getLoggedUser().getId();
		}
		
		Query query = em.createNativeQuery(sql);
		query.setParameter(1, TipoAuditoria.S.name());
		query.setParameter(2, TipoAuditoria.F.name());
		query.setParameter(3, TipoAuditoria.T.name());
		query.setParameter(4, id);
		query.setParameter(5, "%"+searchField.toUpperCase()+"%");
		query.setParameter(6, "%"+searchField.toUpperCase()+"%");
		query.setParameter(7, "%"+searchField.toUpperCase()+"%");	
		query.setParameter(8, "%"+searchField.toUpperCase()+"%");
		query.setFirstResult(pagina);
		query.setMaxResults(max);
		
		List<AuditoriaDto> auditorias = new ArrayList<AuditoriaDto>();
		List<Object> objectList = query.getResultList();		
		for (Object object : objectList) {
			Object[] arrayObject = (Object[]) object;
			
			AuditoriaDto auditoria = new AuditoriaDto();	
			auditoria.setClienteId(((BigInteger) arrayObject[0]).longValue());
			auditoria.setClienteNome((String) arrayObject[1]);
			auditoria.setAnuncioId(((BigInteger) arrayObject[2]).longValue());
			auditoria.setAnuncioNome((String) arrayObject[3]);
			auditoria.setCliquesSite(((BigInteger) arrayObject[4]).longValue());
			auditoria.setCliquesAlbum(((BigInteger) arrayObject[5]).longValue());
			auditoria.setCliquesTelefone(((BigInteger) arrayObject[6]).longValue());
			
			auditorias.add(auditoria);
		}		
		return auditorias;	
				
	}
	
	public Long countListAuditoriaByLoggedUserSearch(String searchField) throws Exception {
		
		String sql = null;
		Long id = null;
		if (representanteDao.findById(Usuario.getLoggedUser().getCliente().getId()) != null) { //corretor
			sql = "SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.representante_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".servico s ON s.id = a.servico_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".ramo r ON r.id = a.ramo_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".usuario u ON u.cliente_id = c.id AND c.id = ?" +
				"WHERE UPPER(pf.nome) LIKE ? OR UPPER(s.nome) LIKE ? OR UPPER(r.nome) LIKE ? OR UPPER(pj.nome) LIKE ? " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome";
			id = Usuario.getLoggedUser().getCliente().getId();;
		} else {
			sql = "SELECT c.id as cliente_id,pf.nome as nome_cliente,a.id as anuncio_id,pj.nome as anuncio," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as site," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as album," +
				"COUNT(case when adt.tipo = ? then 1 else null end) as telefone " +
				"FROM "+this.bundle.getString("jdbc.schema")+".auditoria adt " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".anuncio a ON adt.anuncio_id = a.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".empresa e ON a.empresa_id = e.id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pj ON pj.id = e.pj_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".cliente c ON c.id = a.cliente_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".pessoa pf ON pf.id = c.pessoa_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".servico s ON s.id = a.servico_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".ramo r ON r.id = a.ramo_id " +
				"JOIN "+this.bundle.getString("jdbc.schema")+".usuario u ON u.cliente_id = c.id AND u.id = ?" +
				"WHERE UPPER(pf.nome) LIKE ? OR UPPER(s.nome) LIKE ? OR UPPER(r.nome) LIKE ? OR UPPER(pj.nome) LIKE ? " +
				"GROUP BY c.id,pj.nome,a.id,pf.nome " +
				"ORDER BY pj.nome";
			id = Usuario.getLoggedUser().getId();
		}
		
		Query query = em.createNativeQuery(sql);
		query.setParameter(1, TipoAuditoria.S.name());
		query.setParameter(2, TipoAuditoria.F.name());
		query.setParameter(3, TipoAuditoria.T.name());
		query.setParameter(4, id);
		query.setParameter(5, "%"+searchField.toUpperCase()+"%");
		query.setParameter(6, "%"+searchField.toUpperCase()+"%");
		query.setParameter(7, "%"+searchField.toUpperCase()+"%");
		query.setParameter(8, "%"+searchField.toUpperCase()+"%");
		
		return new Long(query.getResultList().size());
	}

}
